# -*- coding: utf-8 -*-
import sys
sys.path.append("..")

from MysqlCon import MysqlCon
import pandas as pd
import pymysql
import time


class FillAdcode(object):
    """
    """


    #查询数据
    def query_amap(self, connect, cursor):
        connect.ping(reconnect = True)
        query_sql = "SELECT fullname, adcode, lng, lat, boundary FROM dim_amap"
        try:
            cursor.execute(query_sql)
            queryResult = cursor.fetchall()

        except Exception as e:
            print(e)
        #finally:
        #dataf = pd.DataFrame(queryResult)
        #dataf.columns = [ 'full_name', 'adcode', 'lng', 'lat', 'boundary']

        return queryResult

    #多行插入
    def insert_many(self, connect, cursor, insert_sql, insert_data):
        connect.ping(reconnect = True)
        # 批量插入
        try:
            res = cursor.executemany(insert_sql, insert_data)
            print("res = ", res)
            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return True


if __name__ == "__main__":
    # 获取数据库连接
    #mysql
    mysqlConn = pymysql.connect(host='172.28.56.90', user='mysql', password='egSQ7HhxajHZjvdX', port=3306, database='dw',charset="utf8")
    cursor = mysqlConn.cursor()

    #记录执行开始时间
    str_time = int(time.time())

    #实例化类
    obj = FillAdcode()

    amapData = obj.query_amap(mysqlConn, cursor)
    print("total:", len(amapData))
    provinceList, cityList, regionList = [], [], []
    #zxs = ['北京市市辖区', '天津市市辖区', '上海市市辖区', '重庆市市辖区']
    preRow = () #前一行，用于直辖市替换
    province = () #当前城市所属省份
    city = () #当前区县所属城市
    for row in amapData:
        #print(row)
        name = row[0]
        adcode = str(row[1])
        if adcode.endswith("00000") and adcode != '500000': #国家，排除【重庆市】
            continue
        elif adcode.endswith("0000"): #省份
            provinceList.append(row)
            province = row
        elif adcode.endswith("00"): #市
            if name.endswith("市辖区"): #对直辖市做处理
                newRow = province[0:2] + preRow
                city = preRow
                print(province[0:2])
            else :
                newRow = province[0:2] + row
                city = row
            cityList.append(newRow)
        else : #区、县
            newRow = province[0:2] + city[0:2] + row
            regionList.append(newRow)
        preRow = row

        #break
    print("provinceList:", len(provinceList))
    print("cityList:", len(cityList))
    print("regionList:", len(regionList))
    if len(provinceList) > 0 :
        insert_sql = """
            insert into dim_province(
                province_name, province_id, lng, lat, boundary
                ) VALUES (%s, %s, %s, %s, %s)
            """
        #obj.insert_many(mysqlConn, cursor, insert_sql, provinceList)
        #update dim_province set country_id = '100000', country_code = 'CN', country_name = '中华人民共和国', country_lng = '116.3683244', country_lat = '39.9150850';
    if len(cityList) > 0 :
        insert_sql = """
            insert into dim_city(
                province_name, province_id, full_name, city_id, lng, lat, boundary
                ) VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
        #obj.insert_many(mysqlConn, cursor, insert_sql, cityList)
        """
        update dw.`dim_city` c set 
        city_code = IFNULL((select city_code from dw.`dim_city_copy2` cp where cp.city_id = c.city_id), ''), 
        city_name = IFNULL((select city_name from dw.`dim_city_copy2` cp where cp.city_id = c.city_id), ''), 
        big_region = IFNULL((select big_region from dw.`dim_city_copy2` cp where cp.city_id = c.city_id), ''), 
        first_letter = IFNULL((select first_letter from dw.`dim_city_copy2` cp where cp.city_id = c.city_id), ''), 
        city_group = IFNULL((select city_group from dw.`dim_city_copy2` cp where cp.city_id = c.city_id), ''), 
        city_level_desc = IFNULL((select city_level_desc from dw.`dim_city_copy2` cp where cp.city_id = c.city_id), ''), 
        city_level = IFNULL((select city_level from dw.`dim_city_copy2` cp where cp.city_id = c.city_id), 0); 
        """
    if len(regionList) > 0 :
        insert_sql = """
            insert into dim_region(
                province_name, province_id, city_name, city_id, region_name, region_id, lng, lat, boundary
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
        #obj.insert_many(mysqlConn, cursor, insert_sql, regionList)

    mysqlConn.close()

    end_time = int(time.time())
    print("程序执行时间（秒）：", end_time - str_time)
